#!/bin/sh

#mysqlshow -u root | sed -n '4,2000p' | grep '^[^+]' | grep -v 'information_schema' | grep -v 'performance_schema' | awk 'NF>0 {print $2}' | xargs -I {} mysqldump -u root -h 127.0.0.1 -r './{}.sql' {} 

#命令所在目录
MYSQL_SHOW="/usr/local/mysql/bin/mysqlshow"
MYSQL_DUMP="/usr/local/mysql/bin/mysqldump"

#在cron定时任务中使用时，应改为一个相对地址 如：BACKUP_PATH='/home/atie/test'
BACKUP_PATH=`pwd`
BACKUP_PATH="${BACKUP_PATH}/backup"
DB_HOST='192.168.31.211'
DB_USER='root'
DB_PWD='123456'

#此命令在mac下有兼容性问题,linux下已测试正常
DEL_TIME=$(date -d "-2 week" +"%Y%m%d")

SQL_PATH="$BACKUP_PATH/mysql"

if [ -e "$SQL_PATH" ]
then
	echo "delete dir $SQL_PATH"
	`rm -rf $SQL_PATH`
fi
mkdir $SQL_PATH

#导出所有数据库名称
data_names=`$MYSQL_SHOW -h $DB_HOST -u $DB_USER -p$DB_PWD | sed -n '4,2000p' | grep '^[^+]' | grep -v 'information_schema' | grep -v 'performance_schema' | grep -v 'mysql' | awk 'NF>0 {print $2}'`

for dataname in $data_names
do
	echo "backup database ${dataname} -> ${SQL_PATH}/${dataname}.sql"
	tmp=`$MYSQL_DUMP -u $DB_USER -h $DB_HOST -p$DB_PWD -r $SQL_PATH/$dataname.sql $dataname`
done

# tar cjvf mysql.tar.bz2 mysql
# tar czvf mysql.tar.gz mysql

CURRENT_DATE=`date +"%Y%m%d"`
tmp=`tar cjvf $BACKUP_PATH/$CURRENT_DATE.tar.bz2 -C $BACKUP_PATH mysql`
tmp=`rm -rf $SQL_PATH`

# 删除两周前的旧备份文件
for file in `ls ${BACKUP_PATH}`
do
	if [ -f "${BACKUP_PATH}/${file}" ]; then
	    file_time=`echo $file | awk -F "." '{print $1}'`
		[[ $file_time<$DEL_TIME ]] && unlink ${BACKUP_PATH}/${file}
	fi
done
